package pers.jc.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public abstract class Transaction {
	private Connection connection;
	private ArrayList<PreparedStatement> preparedStatements = new ArrayList<>();
	private ArrayList<ResultSet> resultSets = new ArrayList<>();
	private boolean hasCommitted = false;
	
	public Transaction(Access access) {
		boolean error = false;
		try {
			connection = access.getConnection();
			connection.setAutoCommit(false);
			run();
		} catch (Exception e) {
			error = true;
			try {
				if (hasCommitted) {
					connection.rollback();
				}
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			try {
				fail();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			for (ResultSet resultSet : resultSets) {
				if (resultSet != null) {
					try {
						resultSet.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
			for (PreparedStatement preparedStatement : preparedStatements) {
				if (preparedStatement != null) {
					try {
						preparedStatement.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
			if (connection != null) {
				try {
					if (hasCommitted) {
						connection.setAutoCommit(true);
					} else {
						access.closeConnection(connection);
						connection = null;
					}
				} catch (Exception e) {
					access.closeConnection(connection);
					connection = null;
					e.printStackTrace();
				} finally {
					if (connection != null) {
						access.addToPool(connection);
					}
				}
			}
		}
		if (hasCommitted && !error) {
			try {
				success();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public abstract void run() throws Exception;
	
	public void success() {}
	
	public void fail() {}
		
	public void commit() throws Exception {
		this.hasCommitted = true;
		connection.commit();
	}
	
	public int insert(SQL sql) throws Exception {
		PreparedStatement preparedStatement = getPreparedStatement(sql, Statement.NO_GENERATED_KEYS);
		return preparedStatement.executeUpdate();
	}
	
	public int insertAndReturnKey(SQL sql) throws Exception {
		PreparedStatement preparedStatement = getPreparedStatement(sql, Statement.RETURN_GENERATED_KEYS);
		int key = 0;
		int count = preparedStatement.executeUpdate();
		if (count > 0) {
			ResultSet resultSet = preparedStatement.getGeneratedKeys();
			resultSets.add(resultSet);
			resultSet.next();
			key = resultSet.getInt(1);
		}
		return key;
	}
	
	public int update(SQL sql) throws Exception {
		PreparedStatement preparedStatement = getPreparedStatement(sql, Statement.NO_GENERATED_KEYS);
		return preparedStatement.executeUpdate();
	}
	
	public int delete(SQL sql) throws Exception {
		PreparedStatement preparedStatement = getPreparedStatement(sql, Statement.NO_GENERATED_KEYS);
		return preparedStatement.executeUpdate();
	}
	
	private PreparedStatement getPreparedStatement(SQL sql, int autoGeneratedKeys) throws Exception {
		PreparedStatement preparedStatement = connection.prepareStatement(sql.toString(), autoGeneratedKeys);
		preparedStatements.add(preparedStatement);
		return preparedStatement;
	}
	
	public <T> ArrayList<T> select(Class<T> modelClass, SQL sql) throws Exception {
		sql.SELECT_FROM(modelClass);
		return select(modelClass, sql.toString());
	}
	
	public <T> T selectOne(Class<T> modelClass, SQL sql) throws Exception {
		sql.SELECT_FROM(modelClass);
		ArrayList<T> list = select(modelClass, sql.toString());
		if (list.size() > 0) {
			return list.get(0);
		} else {
			return null;
		}
	}
	
	public <T> ArrayList<T> selectAll(Class<T> modelClass) throws Exception {
		return select(modelClass, new SQL(){{
			SELECT_FROM(modelClass);
		}}.toString());
	}
	
	public <T> ArrayList<T> select(Class<T> modelClass, String sql) throws Exception {
		ArrayList<T> list = new ArrayList<>();
		PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.NO_GENERATED_KEYS);
		preparedStatements.add(preparedStatement);
		ResultSet resultSet = preparedStatement.executeQuery();
		resultSets.add(resultSet);
		TableInfo tableInfo = Handle.getTableInfo(modelClass);
		while (resultSet.next()) {
			T model = modelClass.newInstance();
			for (FieldInfo fieldInfo : tableInfo.fieldInfos) {
				fieldInfo.setter.invoke(model, Handle.getResultSetValue(resultSet, fieldInfo.columnLabel, fieldInfo.type));
			}
			list.add(model);
		}
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public <T> int update(T... models) throws Exception {
		if (!Handle.isSameClass(models)) {
			return 0;
		}
		int updateCount = 0;
		TableInfo tableInfo = Handle.getTableInfo(models[0].getClass());
		if (tableInfo.idInfo == null) {
			throw new Exception();
		}
		SQL sql = new SQL(){{
			UPDATE(tableInfo.tableName);
			for (FieldInfo fieldInfo : tableInfo.fieldInfos) {
				if (tableInfo.idInfo.isSameColumn(fieldInfo)) {
					WHERE(fieldInfo.columnLabel + " = ?");
				} else {
					SET(fieldInfo.columnLabel + " = ?");
				}
			}
		}};
		PreparedStatement preparedStatement = getPreparedStatement(sql, Statement.NO_GENERATED_KEYS);
		for (T model : models) {
			int parameterIndex = 1;
			for (FieldInfo fieldInfo : tableInfo.fieldInfos){
				if (tableInfo.idInfo.isSameColumn(fieldInfo)) {
					continue;
				} else {
					Object value = fieldInfo.getter.invoke(model, new Object[]{});
					Handle.setPreparedStatementValue(preparedStatement, parameterIndex, value);
					parameterIndex++;
				}
			}
			Object id = tableInfo.idInfo.getter.invoke(model, new Object[]{});
			Handle.setPreparedStatementValue(preparedStatement, parameterIndex, id);
			preparedStatement.addBatch();
		}
		int[] updateCounts = preparedStatement.executeBatch();
		for (int count : updateCounts) {
			updateCount += count;
		}
		return updateCount;
	}
	
	@SuppressWarnings("unchecked")
	public <T> int insert(int autoGeneratedKeys, T... models) throws Exception {
		if (!Handle.isSameClass(models)) {
			return 0;
		}
		int updateCount = 0;
		TableInfo tableInfo = Handle.getTableInfo(models[0].getClass());
		SQL sql = new SQL(){{
			INSERT_INTO(tableInfo.tableName);
			for (FieldInfo fieldInfo : tableInfo.fieldInfos) {
				if (tableInfo.idInfo != null 
					&& tableInfo.idInfo.isSameColumn(fieldInfo)
					&& tableInfo.idInfo.generatedValue) {
					continue;
				} else {
					VALUES(fieldInfo.columnLabel, "?");
				}
			}
		}};
		PreparedStatement preparedStatement = getPreparedStatement(sql, autoGeneratedKeys);
		for (T model : models) {
			int parameterIndex = 1;
			for (FieldInfo fieldInfo : tableInfo.fieldInfos) {
				if (tableInfo.idInfo != null
					&& tableInfo.idInfo.isSameColumn(fieldInfo)
					&& tableInfo.idInfo.generatedValue) {
					continue;
				} else {
					Object value = fieldInfo.getter.invoke(model, new Object[]{});
					Handle.setPreparedStatementValue(preparedStatement, parameterIndex, value);
					parameterIndex++;
				}
			}
			preparedStatement.addBatch();
		}
		int[] updateCounts = preparedStatement.executeBatch();
		for (int count : updateCounts) {
			updateCount += count;
		}
		if (autoGeneratedKeys == Statement.RETURN_GENERATED_KEYS) {
			ResultSet resultSet = preparedStatement.getGeneratedKeys();
			resultSets.add(resultSet);
			for (int i = 0; i < updateCounts.length; i++) {
				if (updateCounts[i] > 0) {
					resultSet.next();
					int key = resultSet.getInt(1);
					tableInfo.idInfo.setter.invoke(models[i], new Object[]{key});
				}
			}
		}
		return updateCount;
	}
	
	@SuppressWarnings("unchecked")
	public <T> int insert(T... models) throws Exception {
		return insert(Statement.NO_GENERATED_KEYS, models);
	}
	
	@SuppressWarnings("unchecked")
	public <T> int insertAndGenerateKeys(T... models) throws Exception {
		return insert(Statement.RETURN_GENERATED_KEYS, models);
	}
	
	@SuppressWarnings("unchecked")
	public <T> int delete(T... models) throws Exception {
		if (!Handle.isSameClass(models)) {
			return 0;
		}
		int updateCount = 0;
		TableInfo tableInfo = Handle.getTableInfo(models[0].getClass());
		if (tableInfo.idInfo == null) {
			throw new Exception();
		}
		SQL sql = new SQL(){{
			DELETE_FROM(tableInfo.tableName);
			WHERE(tableInfo.idInfo.columnLabel + " = ?");
		}};
		PreparedStatement preparedStatement = getPreparedStatement(sql, Statement.NO_GENERATED_KEYS);
		for (T model : models) {
			Object id = tableInfo.idInfo.getter.invoke(model, new Object[]{});
			Handle.setPreparedStatementValue(preparedStatement, 1, id);
			preparedStatement.addBatch();
		}
		int[] updateCounts = preparedStatement.executeBatch();
		for (int count : updateCounts) {
			updateCount += count;
		}
		return updateCount;
	}
}
